Introduction to pandas 🐼

(Víctor Sojo | vsojo@amnh.org)

In this notebook we will learn how to use pandas, a mighty data-analysis tool for Python. The main object in pandas is the DataFrame, which is a type of dynamic table (similar to an Excel spreadsheet) in which you can access, filter, sort and manipulate columns and rows in extremely efficient and powerful ways.

These pandas.DataFrames are somewhat similar to an MS Excel spreadsheet, but far more powerful. If you have ever worked in R, pandas' DataFrames work very similarly to the ones in R (in fact, in the following lesson we will learn that we can easily convert between one and the other within Jupyter).

References:

Contents

 Getting started
 Creating a pandas.DataFrame
 Exploring the size(s) of a dataframe-of-a-dataframe)
 Accessing columns in a dataframe
 Arithmetic operations across entire columns
 Adding columns to a dataframe
 Deleting or \"dropping\" columns
 Renaming columns
 Applying functions on columns
 Getting the data types of columns
 Boolean operations and filtering/subsetting the rows in a dataframe
 Using .loc() and .iloc() to filter and explore rows and columns-and-.iloc()-to-filter-and-explore-rows-and-columns)
 Using .loc (or .iloc) to replace values in a dataframe-to-replace-values-in-a-dataframe)
 Writing and reading data to/from text files
 Loading in a more realistic dataset
 Use pivot to transform a stacked dataframe to a multi-indexed one
 Flattening a multi-index dataframe
 Resetting the index of a dataframe
 Extracting more than a sample of rows
 Summary statistics
 Merging dataframes
 Comparing two columns
 Replacing specific values in a dataframe with .replace())
 Computing aggregate statistics per group with .groupby())
 Sorting columns
 Plotting pandas data
 Sending pandas dataframes to R and vice versa

Getting started

As customary, let's make sure that we're using the bioinfo environment that we created in the Py201 notebook:

You should see bioinfo being printed out.

If you're on Windows, remember that every line starting with a !, such as !my code should be changed to !wsl my code, and you need to have an active WSL installation.

Importing required libraries

We will need:

Module Use
pandas Python's dataframe tool.
numpy The Python computation optimiser.
matplotlib.pyplot Python's main (but not only!) plotting library.
%matplotlib inline This tells Jupyter to throw plots directly onto the Notebook.

Note that we're giving pyplot an alias of plt, pandas to pd, and numpy to np. This is completely unnecessary but everybody does it.

Creating a pandas.DataFrame

The easiest way of creating a pandas.Dataframe from scratch is with a dictionary, where the keys are the column names and each value is a list of row values for that column.

In the example below we define two columns:

  1. 'threes', contains a few multiples of 3.
  2. 'squares' contains the squares of those numbers.

As you can see, pandas adds an extra indexing column which, in traditional Python fashion, starts at 0 and keeps going up one by one, just like the indices in a list or in a numpy.array.

You may remember two things from previous lessons:

  1. If the last cell is the name of a variable or a simple value, Jupyter prints it out to screen.
  2. I originally recommended that you do not generally make use this behaviour and instead use print() to explicitly print anything that you want printed. I will backtrack from that latter recommendation in the case of pandas, because its dataframes are printed much more nicely if you just write their name in the last line on their own:

⚠️ There are many more ways of creating a pandas dataframe!

There must be at least a dozen ways to create a pandas dataframe. Above we used the dictionary column:[values] method, and below we will read in data from a file. In my view these two are by far the most common and intuitive, but there are many other ways, described in the pandas documentation.

Exploring the size(s) of a dataframe

From the shape, we can trivially extract the number of rows (df.shape[0]) and the number of columns (df.shape[1]). Another very common and extremely efficient way of getting the number of columns is to determine the length of the index column:

And with that, let's look a little closer into how to access columns in a dataframe.

Accessing columns in a dataframe

We can extract columns in a number of ways. Two main ones are using:

We used attribute notation above to get the index column, which is a default column that all pandas dataframes have. But we can also access columns with names that we created:

... and B)dictionary notation (with the key in brackets):

The advantage of object notation is that it looks really neat and is therefore easy to read and quick to write. Conversely, dictionary notation is clunky to write, but it has the advantages that the key can have spaces in its name, and also you can pass the column name (key) as a variable:

Also, dictionary notation is more universal and robust, i.e. it works in most cases, whereas attribute notation does not (e.g. for creating or deleting columns, you must use dict notation).

By the way, you may notice that the last line tells us the name of the column (threes) but it also tells us that its dtype is int64, an integer of 64 bits. You may remember this from the numpy lesson. Indeed, wherever possible, pandas leverages numpy to optimise the contents of dataframes.

Accessing multiple columns

For this, a good way is to use double brackets, with the desired columns separated by commas inside the inner brackets:

In this case, those were our only two columns, so we just get the entire dataframe again, but you can choose whichever columns you wish, in any order. For example, try putting 'squares' first and 'threes' second in the last cell.

Arithmetic operations across entire columns

We can do basic operations on each of the elements of a column very efficiently:

And we can also do operations side-by-side between columns. For example, we can multiply the column with the multiples of 3 by the column that has their squares, to get the cubes:

Our dataframe remains unaltered though. So let's see how to keep the result in a new column if we wish.

Adding columns to a dataframe

For this, you just specify the name of the new column and tell pandas what you want it to contain:

⚠️ Note that you can't use dot.notation for the name of the new column, you must use {'dictionary' : 'notation'}. However, for the values in the calculation, you can use dot notation if you wish (as we did above for the first calculation) or dict notation (as we did for the second calculation)df['names'] = ['zero', 'three', 'six', 'nine', 'twelve', 'fifteen'].

Columns don't have to be calculated from other columns, you can just add columns with whatever content you wish:

Deleting or "dropping" columns

This is trivially done by two methods. One is using Python's del keyword:

Just like for adding columns, you must use dictionary notation to remove a column with del, i.e., if you instead had tried:

del df.squares

you would have gotten an error.

The other method to remove a column is to "drop" it by using the pandas method .drop(columns=[col1, col2, ...]):

⚠️ We need to specify inplace=True so that we make the change into the dataframe itself, as opposed to returning a changed version of it (the default behaviour).

Renaming columns

To rename column(s), pandas uses a dictionary syntax, in which the key is given as old_name and the value is the new_name. For example, to rename both the sqroots and names columns:

⚠️ Just like for dropping a column, we need inplace=True.

Applying functions on columns

You can use the .apply() method to apply any function you wish to every element in a column. This can be a pre-existing python function, or your own function if you wish:

Obviously, if you're just going to double a value it's much easier to just multiply it by two, but you get the idea.

Fancier still, you can use Python's lambda functions to define a novel function on the fly:

A full understanding of the above is beyond the scope of this tutorial, but I encourage you to look into lambda.

Getting the data types of columns

Easy:

It seems the strings are called "object" in pandas.

Changing the data type of a column

One way to change the data type of a column is to reassign it to itself with a different type:

The type of doublenegs has changed from int64 to float32. And if we now print the dataframe again:

... we see that the doublenegs column has indeed changed from integer to float (it now has a decimal; it's zero in all cases, but it's there).

Boolean operations and filtering/subsetting the rows in a dataframe

Row filtering requires a bit of Boolean wizardry: you need to tell pandas which conditions you want your data to match. For example, to find out which of the multiples of three are also even, we ask pandas to show us which of those numbers give a residue (%) of 0 when divided by 2:

And as you can see, the result is a bool.

Filtering a dataframe with df[ filter ]

Now that we have a Boolean filter, we can use it to ask pandas to show us only the elements that match that filter. In our example, to get the rows for which the threes column is even, we use the same filter as above and feed it to a [ ] pair for subsetting:

(the spaces inside the brackets are unnecessary, I just put them there for clarity)

This may seem a little confusing at first, particularly because of the double df outside and inside the brackets. What's happening here is the inner code creates the same list of Trues and Falses that we got above; we then send this list to df, so that pandas can decide what to show us from the dataframe: it shows us those rows for which it gets a True, skips those that have a False.

In fact, you could use any combination of six True/False values:

Specifying multiple conditions with ( ) | ( ) and ( ) & ( )

Let's get all rows for which either the threes column is even, or the value in the num_name column contains the text "een":

Just in case you can't remember from the basic Python workshop how list comprehensions work, here is one to refresh your memory:

Using .loc() and .iloc() to filter and explore rows and columns

There are several ways to access rows. Above we used simply:

df[ df.threes %2 == 0 ]

But this has limitations, and there are other ways worth exploring. One of the most popular and powerful is .loc.

Using .loc to access elements in the dataframe

If you change the first df in the code above to df.loc, we get what seems to be the same result:

You may wonder why you would ever want to write df.loc when df alone suffices. The main advantage of .loc over simple bracket notation is that .loc gives you access to the dataframe itself, so you can make changes to it (as we will see soon), whereas with df[ ] notation you only get a copy of the data, so you can't make any permanent changes to it.

Filtering rows and columns

.loc can filter columns too. All we need is a way of deciding which columns we want. This could simply be by name:

Or you could use separate filters for the row values and column names:

Here we used a list comprehension to tell pandas to get us any column that has the text "leneg" in its name. You can use any valid filter you can come up with.

Ultimately, what needs to happen here is we must give either specific names of the columns, or a list of Trues and Falses for both the rows and the columns:

⚠️ Note that the rows list must be 6 items long because we have six rows, whereas the columns list must be 5 items long because that's the number of columns in this dataframe at present. Any other lengths will throw an error.

Using .iloc (integer location) to access rows and column by position

The .iloc property gives us access to the rows of the dataframe by numbered position, starting at 0:

We can also give a range, which works as customarily in Python:

And we can also access the columns in the same way:

We can take another look at the raw dataframe to make sure that it worked well:

Using .loc (or .iloc) to replace values in a dataframe

Let's create a new column called is_even, and define it to False for starters:

Here's where .loc shows its powers. If we were to try to use df[ condition ][column] to try to change the value, the operation would fail, because df[ ] only produces a copy of the data, it doesn't give access to the dataframe itself. In contrast, .loc both returns what we're looking for, and it gives us access to it, so we can use it to change values:

Note that .iloc functions in a similar way to .loc, i.e. you can use it to change values as well as just look at them.

Writing and reading data to/from text files

Writing to a .csv or .tsv file

This is trivially done. The default is comma-separated values (CSV), but if you want tab-separated (TSV) instead — or something else — you can just specify a separator with sep:

Reading in a .csv or .tsv file

Reading data in is just as easy as writing. We do it with:

df.read_csv('the_file.tsv', sep='\t')

You don't need to specify a separator if the data is in CSV.

Loading in a more realistic dataset

Since we are already familiar with many of the basics of pandas, let's load in a richer dataset (which you have likely downloaded along with this notebook). This dataset contains a few details on phone and internet connectivity per country that I downloaded from the WorldBank. I did a bit of pre-cleaning in Excel and now this is what we have:

Please note that this data is in "stacked" or "record" format, as is most of the data from the UN, WHO, and WorldBank. We need to transform it so that each country appears only once, and we have the indicator values for each year as columns.

Use pivot to transform a stacked dataframe to a multi-indexed one

This is very good. We have a multi-indexed dataframe now. We can now get only the values for 2015:

And if we want to get a column therein, all we have to do is filter twice:

And further down the rabbit hole, if we want the values for a specific country, we filter thrice (using the country code, which is our main index):

Flattening a multi-index dataframe

The multi-index dataframe that we just built is very cool, but it may get a little annoying for some purposes. For example, you can't immediately access the country name as you normally would by issuing:

countries = df['Country Name']

...it throws an error. So, let's flatten that dataframe to make it more like a normal pandas dataframe.

Let's take a look at the columns of the dataframe:

It would seem the indices are tuples. We need to flatten those. But we do want to keep the year, so let's do that through a for loop that combines both the name of the indicator and the year:

Ok, we can do some fancy rearranging and replacement of spaces for underscores _ in the indicator name, and we get:

Good, now we can use this to feed a list of new column names:

Now we can use this list to replace the column names in the dataframe

This is looking good, but you'll notice that we still have a multi-level index. There are many ways to resolve this, but one I would like here is to add a new index to the dataframe, so that it has consecutive numbers as index like a default pandas dataframe. This will have the added advantage of allowing us to now use the Country Code and Country Name as regular pandas columns.

Resetting the index of a dataframe

Resetting the index of a dataframe adds a new 0..1..2..N index to the dataframe, and releases any oreset_index column(s) that may have been previously used as index, turning them into regular columns.

You will see that now our indices are flat.

Even better, we can now access our Country Code and Country Name as regular columns:

Or as multiple columns:

Extracting more than a sample of rows

You will have noticed that pandas is very friendly in not printing the entire rows to screen. But what if you do want them all? You can either print the desired column as a list with list(df.mycol), or, to stay within pandas, you can ask for the .values attribute:

⚠️ Extracting the .values is also helpful for columns with long text, which pandas cuts out by default.⚠️

Summary statistics

General high-level description of the data using .describe()

We can get a very good summary of the data in the dataframe using the .describe() method:

Specific summary statistics (mean, std, median, min, max, count)

The .describe() method provides a general view of several useful summary statistics, such as the mean, count, median (i.e., the 50% quantile), min, max, and standard deviation (std). All of these have their own methods too, which you can apply either on the entire dataframe or on specific columns.

If you run one of these summary methods on the entire dataframe, pandas calculates it for all possible columns:

Summar statistics on the entire dataframe

More typically, you will want to calculate a desired summary statistic on a single column:

Extracting the positions of minimum and maximum values in a dataframe

The .idxmax() and .idxmin() methods give the index of the rows that contain the maximum and minimum values for a given column:

On their own, the indexes of the rows are not very informative, but we can use them to extract the names of the corresponding countries, using .iloc:

Let's load another very interesting set of country indicator data.

Two things worth noting right from the start:

We'll bump into both of these problems below as we attempt to merge the dataframes.

For now, let's look at a high-level summary/description of the contents of the dataframe:

This is very useful. For example, the count row tells us that only 36 countries have an entry for the % Literacy, so these two columns may not be particularly useful, and we may do better to just drop them:

Merging dataframes

We now want to build a Frankensteined dataframe with both sets of data (the one in df and the one in df2). Pandas provides two very handy functions for this purpose: join and merge. With merge, we can specify which column we want to use to merge the two dataframes. Unfortunately, df2 doesn't have a Country Code column, so we will have to use the more dangerous Country Name. This is problematic. See what happens if we just try:

Looks good, the columns are now all together, but take a look at the number of rows (which we do by getting the length of the index column):

It appears we've lost 5 countries! Since we used the country names to merge, that must have been where the problem was. Let's compare the two columns.

Comparing two columns

There are many ways to compare columns (e.g. you could use a for loop and if statements in regular python), but here's a fancy pandas way to find the countries in df2 that are not (~) in the column Country Name of the first df:

(I do not expect you to fully understand what that code is doing. For now, just do what most people do: copy and paste it from StackOverflow and then try to understand it if you want to and have the time, otherwise just make sure that it does what you need and move on)

And let's do it the other way around, to find the countries that are in the first but not the second:

These are the same, but the differences are extremely subtle in terms of spelling, except for Eswatini, which whoever created the second dataframe failed to get the memo that the country is no longer called Swaziland.

The most interesting problem is with the DPRK, which has a ’ in one of the dataframes and a ' in the other... spot the difference? Many thousands of programmer hours are wasted every day because of these.

Replacing specific values in a dataframe with .replace()

Above we used a Boolean filter to change all the values in the dataframe that matched the filter. However, here I cannot think of any clever programmatic way of solving the problem of the five differing names; we will simply have to change each of them one by one. Pandas provides the method .replace(old_value, new_value, inplace=False) for this.

It doesn't matter which of the two dataframes we do it in, or we could do it in both, as long as we end up with the same name in both (e.g. we could make both dataframes eSwatini, which is how I believe they like to spell it there, but we'll keep it simple here).

Let's change the five differing values in the second dataframe only. But since we're clever, we won't do it entirely by hand each time; we'll build a dictionary and do a for loop:

We use inplace=True so that we make the change into the dataframe itself, as opposed to returning a changed version.

Let's look for differences again:

Now the result is empty (there are no items that are in df2 but not in df1), so we can go ahead and create the merged dataframe again:

Very nice. We could now save it to a file so that we can use it later if we wished by running:

wbmerged.to_csv('WB_merged.csv')

Computing aggregate statistics per group with .groupby()

Now that we have a masterfully curated dataset, we can do some actualy data science on it. For example, let's compare the CO2 emissions (metric tons per capita) by income group of the country:

Gosh... if there's a hell, those of us living in high-income nations are probably headed straight there.

Actually, we don't really have to specify a column at the end, we can just get the mean for all values:

Besides mean(), other functions that we may want to use are count(), sum(), min(), max(), std() and median().

Sorting columns

Let's take those latter result and sort them by the most sinful carbon producers:

...well, no surprises there.

How about that 2016 problem?

Our second imported data has only the values for 2016, whereas the first one had 2015 to 2019. If we want to analyse them together, should we keep the 2019 data from the first one since it's more recent, or should we use the 2016 data only so that it matches the status at the time of the other data? Both approaches are defensible, and in your research you'll need to make decisions like this. Just make sure to explain it clearly to yourself and your colleagues/reviewers/peers/readers.

Plotting pandas data

Pandas makes plotting its data extremely straightforward.

Plotting a pandas dataframe is extremely easy:

Changing the default plotting software to Plotly

By default, pandas uses Matplotlib as its default plotting software. This can be changed to the more interactive Plotly:

Now we can use Plotly just like we used Matplotlib above:

You will see that Plotly graphs are dynamic! Scroll over the values to see further details in pop-up clouds. You can also select a box to zoom into, and double-click to get back to full view. Plotly is extremely powerful, and I strongly recommend it if you are considering advanced data visualisation.

Sending pandas dataframes to R and vice versa

You could of course export to TSV or CSV and then import into R in R-Studio, or vice versa, and sometimes that's the best solution. But you can in fact send dataframes between the two languages right here within Jupyter. We will cover that in the Interfacing R with Python lesson, but here's a quick demonstration of how easy this is.

First, we load the R extension for Jupyter:

And now we can declare a full cell as R code, import the dataframe into R, and from then on we just do our normal everyday R, including plotting and such:

⚠️ This was only a brief demonstration; please see the Interfacing R with Python notebook for a more thorough introduction.